
package edu.weber.bookstore.database;

import edu.weber.bookstore.util.DatabaseConnection;
import edu.weber.bookstore.util.DatabaseModel;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * 
 * 
 */
public class Book extends DatabaseModel
{
	/**
	 * table tied to the database
	 */
	protected static String TABLE_NAME = "books";
	private String _name;
	private String _coverImage;
	private int _author_id;
	private Date _publishing_date;
	private String _description;
	private float _price;
	private String _isbn;
	private Author _author;
	
	/**
	 * Constructor for a Book
	 * @param id the primary key of the book
	 * @param name the name of the book
	 * @param coverImage the image of the book's cover
	 * @param author_id the author's primary key associated with the book
	 * @param publishing_date the date the book was published
	 * @param description a description of the book
	 * @param price the price the book is to be sold for
	 * @param isbn the book's isbn
	 */
	public Book(int id, String name, String coverImage, int author_id, Date publishing_date, String description, float price, String isbn)
	{
		super(id);
		_name = name;
		_coverImage = coverImage;
		_author_id = author_id;
		_publishing_date = publishing_date;
		_description = description;
		_price = price;
		_isbn = isbn;
	}
	
	/**
	 * Constructor for a Book
	 * @param id the primary key of the book
	 * @param name the name of the book
	 * @param coverImage the image of the book's cover
	 * @param author_id the author's primary key associated with the book
	 * @param publishing_date the date the book was published
	 * @param description a description of the book
	 * @param price the price the book is to be sold for
	 * @param isbn the book's isbn
	 * @param author The author associated with the author_id.
	 */
	public Book(int id, String name, String coverImage, int author_id, Date publishing_date, String description, float price, String isbn, Author author)
	{
		this(id, name, coverImage, author_id, publishing_date, description, price, isbn);
		_author = author;
	}
	
	/**
	 * Select all books from the database
	 * @return A list of books in the database.
	 * @throws SQLException 
	 */
	public static List<Book> select() throws SQLException
	{
		List<Book> books = new ArrayList<Book>();
		PreparedStatement pm = DatabaseConnection.getConnection().prepareStatement("SELECT * FROM ".concat(TABLE_NAME).concat(";"));
		
		ResultSet rs = pm.executeQuery();
		while(rs.next())
		{
			Book b = resultSetToBook(rs);
			books.add(b);
		}
		return books;
	}
	
	/**
	 * Retrieves a single book.
	 * @param id the primary key to the book
	 * @return a book with the requested primary key or NULL if it is not found.
	 * @throws SQLException 
	 */
	public static Book find(int id) throws SQLException
	{
		Book book = null;
		
		PreparedStatement pm = DatabaseConnection.getConnection().prepareStatement("SELECT * FROM ".concat(TABLE_NAME).concat(" WHERE id = ?;"));
		pm.setInt(1, id);
		
		ResultSet rs = pm.executeQuery();
		if(rs.next())
		{
			book = resultSetToBook(rs);
		}
		return book;
	}
	
	/**
	 * Search books by an author name
	 * @param author a string which may be a substring of a desired author(s)
	 * @return A list of books to which the input string may be similar to their name
	 * @throws SQLException 
	 */
	public static List<Book> searchBookByAuthor(String author) throws SQLException
	{
		author = "%".concat(author).concat("%");
		
		List<Book> books = new ArrayList<Book>();
		PreparedStatement pm = DatabaseConnection.getConnection().prepareStatement("SELECT b.* FROM ".concat(TABLE_NAME).concat(" as b JOIN ").concat(Author.TABLE_NAME).concat(" as a ON b.author_id = a.id WHERE a.fname LIKE ? || a.lname LIKE ? || (fname + ' ' + lname) LIKE ?;"));
		pm.setString(1, author);
		pm.setString(2, author);
		pm.setString(3, author);
		ResultSet rs = pm.executeQuery();
		while(rs.next())
		{
			Book b = resultSetToBook(rs);
			books.add(b);
		}
		return books;
	}
	
	/**
	 * Search a book by it's name/title.
	 * @param name a string which may be a substring of a desired book(s)' title;
	 * @return A list of books which the input string may be similar to the book's name/title.
	 * @throws SQLException 
	 */
	public static List<Book> searchBookByTitle(String name) throws SQLException
	{
		List<Book> books = new ArrayList<Book>();
		PreparedStatement pm = DatabaseConnection.getConnection().prepareStatement("SELECT * FROM ".concat(TABLE_NAME).concat(" WHERE name LIKE ?;"));
		pm.setString(1, "%".concat(name).concat("%"));
		ResultSet rs = pm.executeQuery();
		while(rs.next())
		{
			Book b = resultSetToBook(rs);
			books.add(b);
		}
		return books;
	}
	
	/**
	 * Find a book by the ISBN.
	 * @param isbn a string of an isbn 13.
	 * @return a book if there is one found by the ISBN or null
	 * @throws SQLException 
	 */
	public static Book findBookByIsbn(String isbn) throws SQLException
	{
		Book book = null;
		PreparedStatement pm = DatabaseConnection.getConnection().prepareStatement("SELECT * FROM ".concat(TABLE_NAME).concat(" WHERE isbn LIKE ?;"));
		pm.setString(1, "%".concat(isbn).concat("%"));
		ResultSet rs = pm.executeQuery();
		if(rs.next())
		{
			book = resultSetToBook(rs);
		}
		return book;
	}
	
	/**
	 * Search books by their description
	 * @param descr a possible substring of a book(s) description
	 * @return A list of books found whose description is similar to the input
	 * @throws SQLException 
	 */
	public static List<Book> searchBookByDescription(String descr) throws SQLException
	{
		List<Book> books = new ArrayList<Book>();
		PreparedStatement pm = DatabaseConnection.getConnection().prepareStatement("SELECT * FROM ".concat(TABLE_NAME).concat(" WHERE description LIKE ?;"));
		pm.setString(1, "%".concat(descr).concat("%"));
		ResultSet rs = pm.executeQuery();
		while(rs.next())
		{
			Book b = resultSetToBook(rs);
			books.add(b);
		}
		return books;
	}
	
	/**
	 * Returns a book from a given ResultSet
	 * @param rs the ResultSet from a given SQL query
	 * @return a book which is currently in the queue of the result set.
	 * @throws SQLException 
	 */
	private static Book resultSetToBook(ResultSet rs) throws SQLException
	{
		int author_id = rs.getInt(rs.findColumn("author_id"));
		return new Book(rs.getInt(rs.findColumn("id")), rs.getString(rs.findColumn("name")),
					rs.getString(rs.findColumn("cover_image")), author_id, 
					rs.getDate(rs.findColumn("publishing_date")), rs.getString(rs.findColumn("description")), 
					rs.getFloat(rs.findColumn("price")), rs.getString(rs.findColumn("isbn")),
					Author.find(author_id));
	}
	
	@Override
	protected String getTableName()
	{
		return TABLE_NAME;
	}

	@Override
	protected void create() throws SQLException
	{
		throw new SQLException("No database table tied to this model.");
	}
	
	@Override
	protected void update() throws SQLException
	{
		throw new SQLException("No database table tied to this model.");
	}
	
	/**
	 * 
	 * @return the name of the book
	 */
	public String getName()
	{
		return _name;
	}
	
	/**
	 * 
	 * @return the cover image string for the book
	 */
	public String getCoverImage()
	{
		return _coverImage;
	}
	
	/**
	 * 
	 * @return the author's primary key associated with the book.
	 */
	public int getAuthorId()
	{
		return _author_id;
	}
	
	/**
	 * 
	 * @return the date the book was published
	 */
	public Date getPublishingDate()
	{
		return _publishing_date;
	}
	
	/**
	 * 
	 * @return a full text description of the book.
	 */
	public String getDescription()
	{
		return _description;
	}
	
	/**
	 * 
	 * @return a substring of the description. The substring length is the first 60 characters trimmed or the length of the description
	 */
	public String getShortDescription()
	{
		String shortDesc = _description.substring(0, Math.min(60, _description.length())).trim();
		
		if(shortDesc.length() > 0)
		{
			shortDesc = shortDesc.concat("...");
		}
		
		return shortDesc;
	}
	
	/**
	 * 
	 * @return the selling price of the book
	 */
	public float getPrice()
	{
		return _price;
	}
	
	/**
	 * 
	 * @return the author who wrote the book
	 */
	public Author getAuthor()
	{
		return _author;
	}
	
	/**
	 * 
	 * @return an ISBN 13 string for the book
	 */
	public String getIsbn()
	{
		return _isbn;
	}
	
	/**
	 * Sets the book's name
	 * @param name name of the book
	 */
	public void setName(String name)
	{
		_name = name;
	}
	
	/**
	 * Sets the cover image
	 * @param coverImage a book's cover image filename 
	 */
	public void setCoverImage(String coverImage)
	{
		_coverImage = coverImage;
	}
	
	/**
	 * Sets the author id
	 * @param id primary key for the author associated with the book
	 */
	public void setAuthorId(int id)
	{
		_author_id = id;
	}
	
	/**
	 * Sets the publishing date
	 * @param publishing_date the date the book was published
	 */
	public void setPublishingDate(Date publishing_date)
	{
		_publishing_date = publishing_date;
	}
	
	/**
	 * Sets the description of the book
	 * @param description a text description for the book
	 */
	public void setDescription(String description)
	{
		_description = description;
	}
	
	/**
	 * Sets the book sell price
	 * @param price price of the book that is desired to sell for
	 */
	public void setPrice(float price)
	{
		_price = price;
	}
	
	/**
	 * Sets the book's isbn
	 * @param isbn a String for a isbn 13.
	 */
	public void setIsbn(String isbn)
	{
		_isbn = isbn;
	}
	
	/**
	 * Sets the author and author_id of the book
	 * @param author the author of the book.
	 */
	public void setAuthor(Author author)
	{
		_author = author;
		if(_author != null)
		{
			_author_id = _author.getId();
		}
	}
}
